﻿
CREATE PROCEDURE [dbo].[proc_MarketProject_GetList_In]
(
	@page INT,
	@num INT,
	@T1 INT,
	@T2 INT,
	@beginDate VARCHAR(50),
	@endDate VARCHAR(50),
	@DWBH VARCHAR(30),
	@BMBH VARCHAR(30),
	@flag VARCHAR(10),
	@ss VARCHAR(200),
	@T3 INT,
	@searchText NVARCHAR(100)
)

AS

DECLARE @beginRow INT
DECLARE @endRow INT
DECLARE @str VARCHAR(MAX)


IF @page=1
BEGIN
	SET @beginRow=0
	SET @endRow=@num
END
ELSE
	BEGIN
		SET @beginRow=@num*(@page-1)
		SET @endRow=@num*@page
	END
	
BEGIN
	
SET @str=' '
SET @str=@str+' SELECT row,aaaa.*,Company.CompanyName,p1.ClassName as p1Name ,p2.ClassName as p2Name FROM( '
SET @str=@str+' SELECT ROW_NUMBER() OVER (ORDER BY EditDate DESC ) AS row,* FROM ( '
SET @str=@str+' SELECT distinct mp.id AS nid '
SET @str=@str+' FROM MarketProject mp JOIN MarketProjectItem mpi ON mp.id=mpi.MID '
SET @str=@str+' WHERE 1=1 '
IF @ss!=''
	SET @str=@str+' AND mp.DWBH in ('+@ss+')'
ELSE
	IF @DWBH!='0'
		SET @str=@str+' AND mp.DWBH= '+@DWBH
IF @BMBH!='0'
	SET @str=@str+' AND mp.BMBH= '+@BMBH
IF @t1!=0
	SET @str=@str+' AND mp.T1= '+Convert(varchar(10),@T1)
IF @t2!=0
	SET @str=@str+' AND mp.T2= '+Convert(varchar(10),@T2)
IF @beginDate!='1900-1-1'
	SET @str=@str+' AND mp.BeginDate>= '''+@beginDate+''''
IF @endDate!='2099-1-1'
	SET @str=@str+' AND mp.EndDate<= '''+@endDate+''''
IF @flag!=-100
	SET @str=@str+' AND mp.flag='+@flag
IF @searchText!=''
	BEGIN
		SET @str=@str+' AND ( mp.CityAddress LIKE ''%'+@searchText+'%'' '
		SET @str=@str+' OR mpi.Supplier LIKE ''%'+@searchText+'%'' '
		SET @str=@str+' OR mpi.Contact Like ''%'+@searchText+'%'' '
		SET @str=@str+' OR mpi.Phone Like ''%'+@searchText+'%'' '
		SET @str=@str+' OR mpi.person Like ''%'+@searchText+'%'' '
		SET @str=@str+' OR mpi.Note Like ''%'+@searchText+'%'' ) '
	END
IF @T3!=0
	SET @str=@str+' AND mpi.T3= '+CONVERT(VARCHAR(10),@T3)
SET @str=@str+' ) AS nt JOIN MarketProject mp1 ON nt.nid=mp1.id  '
SET @str=@str+' ) AS aaaa ' 
SET @str=@str+' LEFT JOIN  ProT_ClassT1 p1 ON p1.id = aaaa.T1 '
SET @str=@str+' LEFT JOIN  ProT_ClassT2 p2 ON p2.id = aaaa.T2 '
SET @str=@str+' LEFT JOIN  Company ON Company.DWBH = aaaa.DWBH '
SET @str=@str+' WHERE row>'+CONVERT(VARCHAR(20), @beginRow)+' AND row<='+CONVERT(VARCHAR(20), @endRow) +' '


exec (@str)
	SET NOCOUNT ON;
SELECT @str
END

